# -*- coding:utf-8 -*-
# @Author : mistchan
# @Time : 2021/4/6 0006 17:27
# @File : main.py
# @Software: PyCharm
import json
import pandas as pd
from pandas.api.types import CategoricalDtype
with open('hospital.txt', 'r', encoding='utf-8') as f:
    hospital_list = f.read().split('\n')
hospital_sorted = '|'.join(hospital_list)

df = pd.read_excel('5.31.xlsx', sheet_name='Sheet1')

df_sorted = df.loc[df['客户名称'].str.contains(hospital_sorted)]
# with open('mapping.json', 'r', encoding='utf-8') as fp:
#     mapping = json.load(fp)
# df_sorted['负责人'] = df_sorted['客户名称'].map(
#     mapping
# )
gb = df_sorted.groupby([df['客户名称'], df["品规"]]).agg({"原始销售数量": "sum"})

# # result = gb.to_dict(orient='dict')
# result = gb.to_dict(orient='index')


# with open('mapping.json', 'r', encoding='utf-8') as fp:
#     mapping = json.load(fp)
#
# def do_mapping(x, mapping):
#     return mapping.setdefault(x[0], '无')
# gb['负责人'] = gb.index.get_level_values('客户名称').map(mapping)
# gb.set_index('负责人',inplace=True, drop=False)
gb['医院'] = gb.index.get_level_values('客户名称')
cat_order = CategoricalDtype(
    hospital_list,
    ordered=True
)
gb['医院'] = gb['医院'].astype(cat_order)
gb = gb.sort_values('医院')
gb.to_excel('1.xlsx')